﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;

/*
    LayerBuilder - C# code generator for MS SQLCE / MS SQLServer / OLEDB Layers.
    Copyright (C) 2009 1.xx matricrix
    Copyright (C) 2011 2.xx matricrix
 
    http://layerbuilder.codeplex.com/
    matricrix@gmail.com
   
    License:  GNU General Public License version 2 (GPLv2)
*/


namespace LayerBuilder
{
    /// <summary>
    /// 
    /// </summary>
    public class Layer
    {
        #region
        private string[] IdentLevels = new string[] { "", "\t", "\t\t", "\t\t\t", "\t\t\t\t", "\t\t\t\t\t" };
        private bool GenerateStoredProcedure = false;
        private bool HasIdentityColumn = false;

        private string DBLocalizedNamespaceSuffix = string.Empty;
        private string DBLocalizedNamespacePrefix = string.Empty;
        private string DBLocalizedNamespacePath = string.Empty;

        private List<string> Properties = new List<string>();
        private List<string> Accessors = new List<string>();
        private List<string> ParametersCollection = new List<string>();
        private List<string> LoadDataCollection = new List<string>();
        private List<string> PrimaryKeyColumnsCollection = new List<string>();
        private Hashtable IndexesColumnsCollection = new Hashtable();

        private Hashtable ColumnsIndexesParameterCollection = new Hashtable();
        private Hashtable ColumnsIndexesParameterPassthrowCollection = new Hashtable();
        private Hashtable ColumnsIndexesSQLParameterVariablesCollection = new Hashtable();
        private Hashtable ColumnsIndexesSPSQLParameterVariablesCollection = new Hashtable();
        private Hashtable ColumnsSQLParameterValuesCollection = new Hashtable();

        private string sColumnsKeyParameter = string.Empty;
        private string sColumnsKeyParameterPassthrow = string.Empty;
        private string sColumnsKeySQLParameterVariables = string.Empty;
        private string sColumnsKeySPSQLParameterVariables = string.Empty;
        private string sColumnsKeySQLParameterValues = string.Empty;

        private string sColumnsSQL = string.Empty;
        private string sColumnsSQLVariables = string.Empty;
        private string sColumnsSQLInsert = string.Empty;
        private string sColumnsSQLInsertVariables = string.Empty;
        private string sColumnsSPSQLInsertVariables = string.Empty;
        private string sColumnsSQLSet = string.Empty;
        private string sColumnsSPSQLSet = string.Empty;
        #endregion

        #region Types
        public const string SOURCE_TYPE_MSSQLCE = "MSSQLCE";
        public const string SOURCE_TYPE_MSSQLSRV = "MSSQLSRV";
        public const string SOURCE_TYPE_OLEDB = "OLEDB";
        #endregion

        #region Properties
        public string SourceType { get; set; }
        public string ConnectionString { get; set; }
        public string Table { get; set; }
        public string Namespace { get; set; }
        public string ClassName { get; set; }
        public string OutputFolder { get; set; }
        public bool UseDBLocalizedNamespace { get; set; }
        #endregion

        /// <summary>
        /// 
        /// </summary>
        public Layer()
        {
            this.SourceType = null;
            this.ConnectionString = null;
            this.Table = null;
            this.Namespace = null;
            this.ClassName = null;
            this.OutputFolder = null;
            this.UseDBLocalizedNamespace = false;
        }

        /// <summary>
        /// 
        /// </summary>
        public void Generate()
        {
            if (this.SourceType == null)
                throw new Exception("SourceType is NULL!");

            if (this.ConnectionString == null)
                throw new Exception("ConnectionString is NULL!");

            if (this.Table == null)
                throw new Exception("Table is NULL!");

            if (this.Namespace == null)
                throw new Exception("Namespace is NULL!");

            if (this.ClassName == null)
                throw new Exception("ClassName is NULL!");

            if (this.OutputFolder == null)
                throw new Exception("OutputFolder is NULL!");

            //
            if (this.UseDBLocalizedNamespace)
            {
                switch (this.SourceType)
                {
                    case Layer.SOURCE_TYPE_MSSQLCE:
                        this.DBLocalizedNamespaceSuffix = ".MSSQLCE";
                        this.DBLocalizedNamespacePrefix = "MSSQLCE.";
                        this.DBLocalizedNamespacePath = "\\MSSQLCE";
                        break;

                    case Layer.SOURCE_TYPE_MSSQLSRV:
                        this.DBLocalizedNamespaceSuffix = ".MSSQLSRV";
                        this.DBLocalizedNamespacePrefix = "MSSQLSRV.";
                        this.DBLocalizedNamespacePath = "\\MSSQLSRV";
                        break;

                    case Layer.SOURCE_TYPE_OLEDB:
                        this.DBLocalizedNamespaceSuffix = ".OLEDB";
                        this.DBLocalizedNamespacePrefix = "OLEDB.";
                        this.DBLocalizedNamespacePath = "\\OLEDB";
                        break;
                }
            }
            else
            {
                this.DBLocalizedNamespaceSuffix = string.Empty;
                this.DBLocalizedNamespacePrefix = string.Empty;
                this.DBLocalizedNamespacePath = string.Empty;
            }

            //
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    this.GenerateStoredProcedure = false;
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    this.GenerateStoredProcedure = true;
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    this.GenerateStoredProcedure = true;
                    break;
            }

            #region Primary Key and indexes
            this.EnumPKsIndexes();
            #endregion

            #region Columns
            this.EnumColumns();
            #endregion

            #region BE Entity
            this.GenerateBE();
            #endregion

            #region DAL Layer
            this.GenerateDAL();
            #endregion

            #region BLL Layer
            this.GenerateBLL();
            #endregion

            #region Connection
            this.GenerateConnection();
            #endregion

            #region SQL Script
            if (this.GenerateStoredProcedure)
                this.GenerateSQLScript();
            #endregion
        }

        /// <summary>
        /// 
        /// </summary>
        private void EnumPKsIndexes()
        {
            // Primary Keys
            DataSet dsKeyColumns = null;
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    dsKeyColumns = Microsoft.ApplicationBlocks.Data.CE.SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT KU.COLUMN_NAME, KU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU ON  KU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME   AND KU.TABLE_NAME = TC.TABLE_NAME WHERE TC.TABLE_NAME = @TABLE_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY KU.ORDINAL_POSITION", new System.Data.SqlServerCe.SqlCeParameter("@TABLE_NAME", this.Table));
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    dsKeyColumns = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT KU.COLUMN_NAME, KU.ORDINAL_POSITION FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KU ON  KU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME   AND KU.TABLE_NAME = TC.TABLE_NAME WHERE TC.TABLE_NAME = @TABLE_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY KU.ORDINAL_POSITION", new System.Data.SqlClient.SqlParameter("@TABLE_NAME", this.Table));
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            DataTable dtKeyColumns = dsKeyColumns.Tables[0];
            foreach (DataRow drkeyColumns in dtKeyColumns.Rows)
                this.PrimaryKeyColumnsCollection.Add(((string)drkeyColumns["COLUMN_NAME"]).ToLower());

            // Indexes Columns
            DataSet dsIndexes = null;
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    dsIndexes = Microsoft.ApplicationBlocks.Data.CE.SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT INDEX_NAME, PRIMARY_KEY, COLUMN_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = @TABLE_NAME", new System.Data.SqlServerCe.SqlCeParameter("@TABLE_NAME", this.Table));
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    dsIndexes = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT IDX.NAME INDEX_NAME, IDX.IS_PRIMARY_KEY PRIMARY_KEY, CLM.NAME COLUMN_NAME FROM SYS.OBJECTS OBJ JOIN SYS.INDEXES IDX ON IDX.OBJECT_ID = OBJ.OBJECT_ID JOIN SYS.INDEX_COLUMNS IDX_CLM ON IDX_CLM.OBJECT_ID = IDX.OBJECT_ID AND IDX_CLM.INDEX_ID = IDX.INDEX_ID JOIN SYS.COLUMNS CLM ON CLM.OBJECT_ID = IDX_CLM.OBJECT_ID AND CLM.COLUMN_ID = IDX_CLM.COLUMN_ID WHERE OBJ.NAME = @TABLE_NAME", new System.Data.SqlClient.SqlParameter("@TABLE_NAME", this.Table));
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            DataTable dtIndexes = dsIndexes.Tables[0];
            foreach (DataRow drIndexes in dtIndexes.Rows)
            {
                if (!((bool)drIndexes["PRIMARY_KEY"]))
                {
                    if (!this.IndexesColumnsCollection.ContainsKey((string)drIndexes["COLUMN_NAME"]))
                        this.IndexesColumnsCollection.Add((string)drIndexes["COLUMN_NAME"], new List<string>());

                    ((List<string>)this.IndexesColumnsCollection[(string)drIndexes["COLUMN_NAME"]]).Add((string)drIndexes["INDEX_NAME"]);
                }
            }

        }

        /// <summary>
        /// 
        /// </summary>
        private void EnumColumns()
        {
            bool isNull;
            bool isKey;
            bool isIdentity;

            string columnName;
            string columnType;
            string sqlColumnType;
            string sqlColumnPrecision;


            DataSet dsColumns = null;
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    dsColumns = Microsoft.ApplicationBlocks.Data.CE.SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, AUTOINC_INCREMENT, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME ORDER BY ORDINAL_POSITION", new System.Data.SqlServerCe.SqlCeParameter("@TABLE_NAME", this.Table));
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    dsColumns = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, "SELECT CLM.NAME COLUMN_NAME, CASE CLM.IS_NULLABLE WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' ELSE 'NO' END IS_NULLABLE, TYP.NAME DATA_TYPE, IDT.INCREMENT_VALUE AUTOINC_INCREMENT, CLM.MAX_LENGTH CHARACTER_MAXIMUM_LENGTH, CLM.PRECISION NUMERIC_PRECISION, CLM.SCALE NUMERIC_SCALE FROM SYS.OBJECTS OBJ JOIN SYS.COLUMNS CLM ON	CLM.OBJECT_ID = OBJ.OBJECT_ID JOIN SYS.TYPES TYP ON TYP.SYSTEM_TYPE_ID = CLM.SYSTEM_TYPE_ID AND	TYP.USER_TYPE_ID = CLM.USER_TYPE_ID LEFT JOIN SYS.IDENTITY_COLUMNS IDT ON IDT.OBJECT_ID = OBJ.OBJECT_ID AND IDT.COLUMN_ID = CLM.COLUMN_ID WHERE OBJ.NAME = @TABLE_NAME ORDER BY CLM.COLUMN_ID", new System.Data.SqlClient.SqlParameter("@TABLE_NAME", this.Table));
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }

            DataTable dtColumns = dsColumns.Tables[0];
            foreach (DataRow drColumns in dtColumns.Rows)
            {
                columnName = (string)drColumns["COLUMN_NAME"];
                sqlColumnType = (string)drColumns["DATA_TYPE"];
                sqlColumnPrecision = string.Empty;

                isNull = ((string)drColumns["IS_NULLABLE"]) == "YES";
                isKey = this.PrimaryKeyColumnsCollection.Contains(columnName.ToLower());
                isIdentity = drColumns["AUTOINC_INCREMENT"] != DBNull.Value;

                if (isKey && isIdentity)
                    this.HasIdentityColumn = true;

                switch (sqlColumnType.ToLower())
                {
                    case "datetime":
                        columnType = "DateTime";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "bit":
                        columnType = "bool";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "bigint":
                        columnType = "long";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "smallint":
                    case "tinyint":
                        columnType = "short";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "int":
                    case "integer":
                        columnType = "int";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "float":
                        columnType = "float";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "real":
                        columnType = "double";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "money":
                        columnType = "decimal";
                        if (isNull)
                            columnType += "?";
                        break;

                    case "numeric":
                        columnType = "decimal";
                        if (isNull)
                            columnType += "?";
                        sqlColumnPrecision = "(" + drColumns["NUMERIC_PRECISION"].ToString() + ", " + drColumns["NUMERIC_SCALE"].ToString() + ")";
                        break;

                    case "image":
                    case "binary":
                    case "varbinary":
                        columnType = "byte[]";
                        sqlColumnPrecision = "(" + drColumns["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
                        break;

                    case "text":
                    case "ntext":
                        columnType = "string";
                        break;

                    case "char":
                    case "nchar":
                    case "varchar":
                    case "nvarchar":
                        columnType = "string";
                        sqlColumnPrecision = "(" + drColumns["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")";
                        break;

                    default:
                        columnType = "|" + ((string)drColumns["DATA_TYPE"]) + "|";
                        break;
                }

                //
                this.Properties.Add(this.IdentLevels[0] + "private " + columnType + " _" + columnName.ToLower() + ";");

                //
                this.Accessors.Add(this.IdentLevels[0] + "/// <summary>");
                this.Accessors.Add(this.IdentLevels[0] + "/// Column " + this.Table + "." + columnName);
                this.Accessors.Add(this.IdentLevels[0] + "/// </summary>");
                this.Accessors.Add(this.IdentLevels[0] + "public " + columnType + " " + columnName.ToUpper());
                this.Accessors.Add(this.IdentLevels[0] + "{");
                this.Accessors.Add(this.IdentLevels[1] + "get { return this._" + columnName.ToLower() + "; }");
                this.Accessors.Add(this.IdentLevels[1] + "set { this._" + columnName.ToLower() + " = value; }");
                this.Accessors.Add(this.IdentLevels[0] + "}");
                this.Accessors.Add(this.IdentLevels[0] + string.Empty);

                //
                switch (this.SourceType)
                {
                    case Layer.SOURCE_TYPE_MSSQLCE:
                        this.ParametersCollection.Add(this.IdentLevels[0] + "oParameters.Add(new System.Data.SqlServerCe.SqlCeParameter(\"@" + columnName.ToUpper() + "\", " + (isNull ? "(this._" + columnName.ToLower() + " == null ? (object)DBNull.Value : this._" + columnName.ToLower() + ")" : "this._" + columnName.ToLower()) + "));");
                        break;

                    case Layer.SOURCE_TYPE_MSSQLSRV:
                        this.ParametersCollection.Add(this.IdentLevels[0] + "oParameters.Add(new System.Data.SqlClient.SqlParameter(\"@" + columnName.ToUpper() + "\", " + (isNull ? "(this._" + columnName.ToLower() + " == null ? (object)DBNull.Value : this._" + columnName.ToLower() + ")" : "this._" + columnName.ToLower()) + "));");
                        break;

                    case Layer.SOURCE_TYPE_OLEDB:
                        break;
                }

                //
                this.LoadDataCollection.Add(this.IdentLevels[0] + "oReturn." + columnName.ToUpper() + " = " + (isNull ? "(oRow[\"" + columnName.ToUpper() + "\"] == DBNull.Value ? (null) : ((" + columnType + ")oRow[\"" + columnName.ToUpper() + "\"])" : "(" + columnType + ")oRow[\"" + columnName.ToUpper() + "\"]") + ";");

                //
                if (isKey)
                {
                    if (sColumnsKeyParameter.Length > 0)
                        sColumnsKeyParameter += ", ";
                    sColumnsKeyParameter += columnType + " " + columnName.ToLower();

                    if (sColumnsKeyParameterPassthrow.Length > 0)
                        sColumnsKeyParameterPassthrow += ", ";
                    sColumnsKeyParameterPassthrow += columnName.ToLower();

                    if (sColumnsKeySQLParameterVariables.Length > 0)
                        sColumnsKeySQLParameterVariables += " AND ";
                    sColumnsKeySQLParameterVariables += columnName.ToUpper() + " = @" + columnName.ToUpper();

                    if (sColumnsKeySPSQLParameterVariables.Length > 0)
                        sColumnsKeySPSQLParameterVariables += ", ";
                    sColumnsKeySPSQLParameterVariables += "@" + columnName.ToUpper() + " " + sqlColumnType + sqlColumnPrecision;

                    if (sColumnsKeySQLParameterValues.Length > 0)
                        sColumnsKeySQLParameterValues += ", ";
                    switch (this.SourceType)
                    {
                        case Layer.SOURCE_TYPE_MSSQLCE:
                            sColumnsKeySQLParameterValues += "new System.Data.SqlServerCe.SqlCeParameter(\"@" + columnName.ToUpper() + "\", " + (isNull ? "(" + columnName.ToLower() + " == null ? (object)DBNull.Value : " + columnName.ToLower() + ")" : columnName.ToLower()) + ")";
                            break;

                        case Layer.SOURCE_TYPE_MSSQLSRV:
                            sColumnsKeySQLParameterValues += "new System.Data.SqlClient.SqlParameter(\"@" + columnName.ToUpper() + "\", " + (isNull ? "(" + columnName.ToLower() + " == null ? (object)DBNull.Value : " + columnName.ToLower() + ")" : columnName.ToLower()) + ")";
                            break;

                        case Layer.SOURCE_TYPE_OLEDB:
                            break;
                    }
                }
                else
                {
                    if (sColumnsSQLSet.Length > 0)
                        sColumnsSQLSet += ", ";
                    sColumnsSQLSet += columnName.ToUpper() + " = @" + columnName.ToUpper();

                    if (sColumnsSPSQLSet.Length > 0)
                        sColumnsSPSQLSet += ", ";
                    sColumnsSPSQLSet += "@" + columnName.ToUpper() + " " + sqlColumnType + sqlColumnPrecision;

                }

                if (sColumnsSQL.Length > 0)
                    sColumnsSQL += ", ";
                sColumnsSQL += columnName.ToUpper();

                if (sColumnsSQLVariables.Length > 0)
                    sColumnsSQLVariables += ", ";
                sColumnsSQLVariables += "@" + columnName.ToUpper();

                if (!(isKey && isIdentity))
                {
                    if (sColumnsSQLInsert.Length > 0)
                        sColumnsSQLInsert += ", ";
                    sColumnsSQLInsert += columnName.ToUpper();

                    if (sColumnsSQLInsertVariables.Length > 0)
                        sColumnsSQLInsertVariables += ", ";
                    sColumnsSQLInsertVariables += "@" + columnName.ToUpper();

                    if (sColumnsSPSQLInsertVariables.Length > 0)
                        sColumnsSPSQLInsertVariables += ", ";
                    sColumnsSPSQLInsertVariables += "@" + columnName.ToUpper() + " " + sqlColumnType + sqlColumnPrecision;
                }

                //
                if (this.IndexesColumnsCollection.ContainsKey(columnName))
                {
                    foreach (string indexName in ((List<string>)this.IndexesColumnsCollection[columnName]))
                    {
                        // ColumnsIndexesParameterCollection
                        if (!this.ColumnsIndexesParameterCollection.ContainsKey(indexName))
                            this.ColumnsIndexesParameterCollection.Add(indexName, string.Empty);

                        string sColumnsIndexesParameter = (string)this.ColumnsIndexesParameterCollection[indexName];

                        if (sColumnsIndexesParameter.Length > 0)
                            sColumnsIndexesParameter += ", ";
                        sColumnsIndexesParameter += columnType + " " + columnName.ToLower();

                        this.ColumnsIndexesParameterCollection[indexName] = sColumnsIndexesParameter;

                        // columnsIndexesParameterPassthrowCollection
                        if (!this.ColumnsIndexesParameterPassthrowCollection.ContainsKey(indexName))
                            this.ColumnsIndexesParameterPassthrowCollection.Add(indexName, string.Empty);

                        string sColumnsIndexesParameterPassthrow = (string)this.ColumnsIndexesParameterPassthrowCollection[indexName];

                        if (sColumnsIndexesParameterPassthrow.Length > 0)
                            sColumnsIndexesParameterPassthrow += ", ";
                        sColumnsIndexesParameterPassthrow += columnName.ToLower();

                        this.ColumnsIndexesParameterPassthrowCollection[indexName] = sColumnsIndexesParameterPassthrow;

                        // ColumnsIndexesSQLParameterVariablesCollection
                        if (!this.ColumnsIndexesSQLParameterVariablesCollection.ContainsKey(indexName))
                            this.ColumnsIndexesSQLParameterVariablesCollection.Add(indexName, string.Empty);

                        string sColumnsIndexesSQLParameterVariables = (string)this.ColumnsIndexesSQLParameterVariablesCollection[indexName];

                        if (sColumnsIndexesSQLParameterVariables.Length > 0)
                            sColumnsIndexesSQLParameterVariables += " AND ";
                        sColumnsIndexesSQLParameterVariables += columnName.ToUpper() + " = @" + columnName.ToUpper();

                        this.ColumnsIndexesSQLParameterVariablesCollection[indexName] = sColumnsIndexesSQLParameterVariables;

                        // ColumnsIndexesSPSQLParameterVariablesCollection
                        if (!this.ColumnsIndexesSPSQLParameterVariablesCollection.ContainsKey(indexName))
                            this.ColumnsIndexesSPSQLParameterVariablesCollection.Add(indexName, string.Empty);

                        string sColumnsIndexesSPSQLParameterVariables = (string)this.ColumnsIndexesSPSQLParameterVariablesCollection[indexName];

                        if (sColumnsIndexesSPSQLParameterVariables.Length > 0)
                            sColumnsIndexesSPSQLParameterVariables += ", ";
                        sColumnsIndexesSPSQLParameterVariables += "@" + columnName.ToUpper() + " " + sqlColumnType + sqlColumnPrecision;

                        this.ColumnsIndexesSPSQLParameterVariablesCollection[indexName] = sColumnsIndexesSPSQLParameterVariables;

                        // ColumnsSQLParameterValuesCollection
                        if (!this.ColumnsSQLParameterValuesCollection.ContainsKey(indexName))
                            this.ColumnsSQLParameterValuesCollection.Add(indexName, string.Empty);

                        string sColumnsIndexesSQLParameterValues = (string)this.ColumnsSQLParameterValuesCollection[indexName];

                        if (sColumnsIndexesSQLParameterValues.Length > 0)
                            sColumnsIndexesSQLParameterValues += ", ";
                        switch (this.SourceType)
                        {
                            case Layer.SOURCE_TYPE_MSSQLCE:
                                sColumnsIndexesSQLParameterValues += "new System.Data.SqlServerCe.SqlCeParameter(\"@" + columnName.ToUpper() + "\", " + (isNull ? "(" + columnName.ToLower() + " == null ? (object)DBNull.Value : " + columnName.ToLower() + ")" : columnName.ToLower()) + ")";
                                break;

                            case Layer.SOURCE_TYPE_MSSQLSRV:
                                sColumnsIndexesSQLParameterValues += "new System.Data.SqlClient.SqlParameter(\"@" + columnName.ToUpper() + "\", " + (isNull ? "(" + columnName.ToLower() + " == null ? (object)DBNull.Value : " + columnName.ToLower() + ")" : columnName.ToLower()) + ")";
                                break;

                            case Layer.SOURCE_TYPE_OLEDB:
                                break;
                        }

                        this.ColumnsSQLParameterValuesCollection[indexName] = sColumnsIndexesSQLParameterValues;
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        private void GenerateBE()
        {
            //
            StreamWriter swBE;
            string sBEFolder = this.OutputFolder + this.DBLocalizedNamespacePath + "\\BE";
            if (!Directory.Exists(sBEFolder))
                Directory.CreateDirectory(sBEFolder);
            swBE = new StreamWriter(sBEFolder + "\\" +this.ClassName + ".cs", false);

            //
            swBE.WriteLine(this.IdentLevels[0] + "using System;");
            swBE.WriteLine(this.IdentLevels[0] + "using System.Collections;");
            swBE.WriteLine(this.IdentLevels[0] + "using System.Collections.Generic;");
            swBE.WriteLine(this.IdentLevels[0] + "using System.Text;");
            swBE.WriteLine(this.IdentLevels[0] + string.Empty);

            swBE.WriteLine(this.IdentLevels[0] + "namespace " + this.Namespace + this.DBLocalizedNamespaceSuffix + ".BE");
            swBE.WriteLine(this.IdentLevels[0] + "{");

            swBE.WriteLine(this.IdentLevels[1] + "/// <summary>");
            swBE.WriteLine(this.IdentLevels[1] + "/// Table " + this.Table);
            swBE.WriteLine(this.IdentLevels[1] + "/// ");
            swBE.WriteLine(this.IdentLevels[1] + "/// Generated by C# LayerBuilder");
            swBE.WriteLine(this.IdentLevels[1] + "/// " + DateTime.Now.ToString());
            swBE.WriteLine(this.IdentLevels[1] + "/// ");
            swBE.WriteLine(this.IdentLevels[1] + "/// http://layerbuilder.codeplex.com/");
            swBE.WriteLine(this.IdentLevels[1] + "/// ");
            swBE.WriteLine(this.IdentLevels[1] + "/// </summary>");
            swBE.WriteLine(this.IdentLevels[1] + "public class " +this.ClassName);
            swBE.WriteLine(this.IdentLevels[1] + "{");

            #region this.Properties
            foreach (string s in this.Properties)
                swBE.WriteLine(this.IdentLevels[2] + s);
            swBE.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region this.Accessors
            foreach (string s in this.Accessors)
                swBE.WriteLine(this.IdentLevels[2] + s);
            swBE.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region ItemParameter
            swBE.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBE.WriteLine(this.IdentLevels[2] + "/// Entity this.Properties");
            swBE.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBE.WriteLine(this.IdentLevels[2] + "/// <returns>Collection this.Properties</returns>");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swBE.WriteLine(this.IdentLevels[2] + "public System.Data.SqlServerCe.SqlCeParameter[] ItemParameter()");
                    swBE.WriteLine(this.IdentLevels[2] + "{");
                    swBE.WriteLine(this.IdentLevels[3] + "List<System.Data.SqlServerCe.SqlCeParameter> oParameters = new List<System.Data.SqlServerCe.SqlCeParameter>();");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swBE.WriteLine(this.IdentLevels[2] + "public System.Data.SqlClient.SqlParameter[] ItemParameter()");
                    swBE.WriteLine(this.IdentLevels[2] + "{");
                    swBE.WriteLine(this.IdentLevels[3] + "List<System.Data.SqlClient.SqlParameter> oParameters = new List<System.Data.SqlClient.SqlParameter>();");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            foreach (string s in this.ParametersCollection)
                swBE.WriteLine(this.IdentLevels[3] + s);
            swBE.WriteLine(this.IdentLevels[3] + "return oParameters.ToArray();");
            swBE.WriteLine(this.IdentLevels[2] + "}");
            #endregion

            swBE.WriteLine(this.IdentLevels[1] + "}");

            swBE.WriteLine(this.IdentLevels[0] + "}");


            //
            swBE.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        private void GenerateDAL()
        {
            //
            StreamWriter swDALBase;
            StreamWriter swDAL;
            string sDALFolder = this.OutputFolder + this.DBLocalizedNamespacePath + "\\DAL";
            if (!Directory.Exists(sDALFolder))
                Directory.CreateDirectory(sDALFolder);
            swDALBase = new StreamWriter(sDALFolder + "\\" +this.ClassName + "_Base_DAL.cs", false);
            swDAL = new StreamWriter(sDALFolder + "\\_temp_" +this.ClassName + "_DAL.cs", false);

            //
            #region DALBase
            swDALBase.WriteLine(this.IdentLevels[0] + "using System;");
            swDALBase.WriteLine(this.IdentLevels[0] + "using System.Collections.Generic;");
            swDALBase.WriteLine(this.IdentLevels[0] + "using System.Text;");
            swDALBase.WriteLine(this.IdentLevels[0] + "using System.Data;");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[0] + "using System.Data.SqlServerCe;");
                    swDALBase.WriteLine(this.IdentLevels[0] + "using SqlCE = Microsoft.ApplicationBlocks.Data.CE;");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[0] + "using SqlSrv = Microsoft.ApplicationBlocks.Data;");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);

            swDALBase.WriteLine(this.IdentLevels[0] + "namespace " + this.Namespace + this.DBLocalizedNamespaceSuffix + ".DAL");
            swDALBase.WriteLine(this.IdentLevels[0] + "{");

            swDALBase.WriteLine(this.IdentLevels[1] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[1] + "/// Table " + this.Table);
            swDALBase.WriteLine(this.IdentLevels[1] + "/// ");
            swDALBase.WriteLine(this.IdentLevels[1] + "/// Generated by C# LayerBuilder");
            swDALBase.WriteLine(this.IdentLevels[1] + "/// " + DateTime.Now.ToString());
            swDALBase.WriteLine(this.IdentLevels[1] + "/// ");
            swDALBase.WriteLine(this.IdentLevels[1] + "/// http://layerbuilder.codeplex.com/");
            swDALBase.WriteLine(this.IdentLevels[1] + "/// ");
            swDALBase.WriteLine(this.IdentLevels[1] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[1] + "public class " +this.ClassName + "_Base_DAL");
            swDALBase.WriteLine(this.IdentLevels[1] + "{");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);

            #region Load
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Load entity");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "private static " + this.DBLocalizedNamespacePrefix + "BE." +this.ClassName + " Load(DataRow oRow)");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            swDALBase.WriteLine(this.IdentLevels[3] + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " oReturn = new " + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + "();");

            foreach (string s in this.LoadDataCollection)
                swDALBase.WriteLine(this.IdentLevels[3] + s);

            swDALBase.WriteLine(this.IdentLevels[3] + "return oReturn;");
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Insert
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Insert entity");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static int Insert(" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " o" + this.ClassName + ")");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    if (this.HasIdentityColumn)
                    {
                        swDALBase.WriteLine(this.IdentLevels[3] + "SqlCE.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"INSERT INTO " + this.Table.ToUpper() + " (" + sColumnsSQLInsert + ") VALUES (" + sColumnsSQLInsertVariables + ")\", o" + this.ClassName + ".ItemParameter());");
                        swDALBase.WriteLine(this.IdentLevels[3] + "return SqlCE.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"SELECT @@IDENTITY\");");
                    }
                    else
                        swDALBase.WriteLine(this.IdentLevels[3] + "return SqlCE.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"INSERT INTO " + this.Table.ToUpper() + " (" + sColumnsSQLInsert + ") VALUES (" + sColumnsSQLInsertVariables + ")\", o" + this.ClassName + ".ItemParameter());");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlSrv.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_INSERT\", o" + this.ClassName + ".ItemParameter());");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Select
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Search entity");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static " + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " Select(" + sColumnsKeyParameter + ")");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlCE.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper() + " WHERE " + sColumnsKeySQLParameterVariables + "\", new System.Data.SqlServerCe.SqlCeParameter[]{ " + sColumnsKeySQLParameterValues + " });");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlSrv.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_SELECT\", new System.Data.SqlClient.SqlParameter[]{ " + sColumnsKeySQLParameterValues + " });");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[3] + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " o" + this.ClassName + " = null;");
            swDALBase.WriteLine(this.IdentLevels[3] + "if (ds" +this.ClassName + ".Tables[0].Rows.Count > 0)");
            swDALBase.WriteLine(this.IdentLevels[4] + "o" +this.ClassName + " = " +this.ClassName + "_Base_DAL.Load(ds" +this.ClassName + ".Tables[0].Rows[0]);");
            swDALBase.WriteLine(this.IdentLevels[3] + "return o" +this.ClassName + ";");
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Select_ Indexes
            foreach (string indexName in this.ColumnsIndexesParameterCollection.Keys)
            {
                swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
                swDALBase.WriteLine(this.IdentLevels[2] + "/// Search entity by index " + indexName);
                swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
                swDALBase.WriteLine(this.IdentLevels[2] + "public static List<" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + "> Select_" + indexName + "(" + (string)this.ColumnsIndexesParameterCollection[indexName] + ")");
                swDALBase.WriteLine(this.IdentLevels[2] + "{");
                switch (this.SourceType)
                {
                    case Layer.SOURCE_TYPE_MSSQLCE:
                        swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlCE.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper() + " WHERE " + (string)this.ColumnsIndexesSQLParameterVariablesCollection[indexName] + "\", new System.Data.SqlServerCe.SqlCeParameter[]{ " + (string)this.ColumnsSQLParameterValuesCollection[indexName] + " });");
                        break;

                    case Layer.SOURCE_TYPE_MSSQLSRV:
                        swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlSrv.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_FILL_" + indexName.ToUpper() + "\",  new System.Data.SqlClient.SqlParameter[]{ " + (string)this.ColumnsSQLParameterValuesCollection[indexName] + " });");
                        break;

                    case Layer.SOURCE_TYPE_OLEDB:
                        break;
                }
                swDALBase.WriteLine(this.IdentLevels[3] + "List<" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + "> oList" + this.ClassName + " = new List<" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + ">();");
                swDALBase.WriteLine(this.IdentLevels[3] + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " o" + this.ClassName + " = null;");
                
                swDALBase.WriteLine(this.IdentLevels[3] + "foreach(DataRow dr in " + this.ClassName + ".Tables[0].Rows)");
                swDALBase.WriteLine(this.IdentLevels[3] + "{");
                swDALBase.WriteLine(this.IdentLevels[4] + "o" + this.ClassName + " = " + this.ClassName + "_Base_DAL.Load(dr);");
                swDALBase.WriteLine(this.IdentLevels[4] + "oList" + this.ClassName + ".Add(o" + this.ClassName + ");");
                swDALBase.WriteLine(this.IdentLevels[3] + "}");
                swDALBase.WriteLine(this.IdentLevels[3] + "return oList" + this.ClassName + ";");
                swDALBase.WriteLine(this.IdentLevels[2] + "}");
                swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            }
            #endregion

            #region Exists
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Check if entity exists");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static bool Exists(" + sColumnsKeyParameter + ")");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return Convert.ToInt32(SqlCE.SqlHelper.ExecuteScalar(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"SELECT COUNT(*) FROM " + this.Table.ToUpper() + " WHERE " + sColumnsKeySQLParameterVariables + "\", new System.Data.SqlServerCe.SqlCeParameter[] { " + sColumnsKeySQLParameterValues + " })) > 0;");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return Convert.ToInt32(SqlSrv.SqlHelper.ExecuteScalar(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_EXISTS\", new System.Data.SqlClient.SqlParameter[] { " + sColumnsKeySQLParameterValues + " })) > 0;");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Fill
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Fill entity list");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static DataTable Fill()");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlCE.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper() + "\");");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlSrv.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_FILL\");");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[3] + "return ds" +this.ClassName + ".Tables[0];");
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Fill_ Indexes
            foreach (string indexName in this.ColumnsIndexesParameterCollection.Keys)
            {
                swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
                swDALBase.WriteLine(this.IdentLevels[2] + "/// Search entity by index " + indexName);
                swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
                swDALBase.WriteLine(this.IdentLevels[2] + "public static DataTable Fill_" + indexName + "(" + (string)this.ColumnsIndexesParameterCollection[indexName] + ")");
                swDALBase.WriteLine(this.IdentLevels[2] + "{");
                switch (this.SourceType)
                {
                    case Layer.SOURCE_TYPE_MSSQLCE:
                        swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlCE.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper() + " WHERE " + (string)this.ColumnsIndexesSQLParameterVariablesCollection[indexName] + "\", new System.Data.SqlServerCe.SqlCeParameter[]{" + (string)this.ColumnsSQLParameterValuesCollection[indexName] + " });");
                        break;

                    case Layer.SOURCE_TYPE_MSSQLSRV:
                        swDALBase.WriteLine(this.IdentLevels[3] + "DataSet ds" + this.ClassName + " = SqlSrv.SqlHelper.ExecuteDataset(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_FILL_" + indexName.ToUpper() + "\", new System.Data.SqlClient.SqlParameter[]{" + (string)this.ColumnsSQLParameterValuesCollection[indexName] + " });");
                        break;

                    case Layer.SOURCE_TYPE_OLEDB:
                        break;
                }
                swDALBase.WriteLine(this.IdentLevels[3] + "return ds" +this.ClassName + ".Tables[0];");
                swDALBase.WriteLine(this.IdentLevels[2] + "}");
                swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            }
            #endregion

            #region Update
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Update entity");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static int Update(" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " o" + this.ClassName + ")");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlCE.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"UPDATE " + this.Table.ToUpper() + " SET " + sColumnsSQLSet + " WHERE " + sColumnsKeySQLParameterVariables + "\", o" + this.ClassName + ".ItemParameter());");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlSrv.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_UPDATE\", o" + this.ClassName + ".ItemParameter());");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            swDALBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Delete
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Delete entity record");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static int Delete(" + sColumnsKeyParameter + ")");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlCE.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"DELETE " + this.Table.ToUpper() + " WHERE " + sColumnsKeySQLParameterVariables + "\", new System.Data.SqlServerCe.SqlCeParameter[] { " + sColumnsKeySQLParameterValues + " });");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlSrv.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_DELETE\", new System.Data.SqlClient.SqlParameter[] { " + sColumnsKeySQLParameterValues + " });");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            #endregion

            #region DeleteAll
            swDALBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// Delete entity ALL records");
            swDALBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDALBase.WriteLine(this.IdentLevels[2] + "public static int DeleteAll()");
            swDALBase.WriteLine(this.IdentLevels[2] + "{");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlCE.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.Text, \"TRUNCATE TABLE " + this.Table.ToUpper() + "\");");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDALBase.WriteLine(this.IdentLevels[3] + "return SqlSrv.SqlHelper.ExecuteNonQuery(" + this.DBLocalizedNamespacePrefix + "Connection.ConnectionString, CommandType.StoredProcedure, \"dbo.PRC_" + this.Table.ToUpper() + "_DELETEALL\");");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDALBase.WriteLine(this.IdentLevels[2] + "}");
            #endregion

            swDALBase.WriteLine(this.IdentLevels[1] + "}");

            swDALBase.WriteLine(this.IdentLevels[0] + "}");
            #endregion

            #region DAL
            swDAL.WriteLine(this.IdentLevels[0] + "using System;");
            swDAL.WriteLine(this.IdentLevels[0] + "using System.Collections.Generic;");
            swDAL.WriteLine(this.IdentLevels[0] + "using System.Text;");
            swDAL.WriteLine(this.IdentLevels[0] + "using System.Data;");
            switch (this.SourceType)
            {
                case Layer.SOURCE_TYPE_MSSQLCE:
                    swDAL.WriteLine(this.IdentLevels[0] + "using System.Data.SqlServerCe;");
                    swDAL.WriteLine(this.IdentLevels[0] + "using SqlCE = Microsoft.ApplicationBlocks.Data.CE;");
                    break;

                case Layer.SOURCE_TYPE_MSSQLSRV:
                    swDAL.WriteLine(this.IdentLevels[0] + "using SqlSrv = Microsoft.ApplicationBlocks.Data;");
                    break;

                case Layer.SOURCE_TYPE_OLEDB:
                    break;
            }
            swDAL.WriteLine(this.IdentLevels[0] + string.Empty);

            swDAL.WriteLine(this.IdentLevels[0] + "namespace " + this.Namespace + this.DBLocalizedNamespaceSuffix + ".DAL");
            swDAL.WriteLine(this.IdentLevels[0] + "{");

            swDAL.WriteLine(this.IdentLevels[1] + "/// <summary>");
            swDAL.WriteLine(this.IdentLevels[1] + "/// Table " + this.Table);
            swDAL.WriteLine(this.IdentLevels[1] + "/// ");
            swDAL.WriteLine(this.IdentLevels[1] + "/// Generated by C# LayerBuilder");
            swDAL.WriteLine(this.IdentLevels[1] + "/// " + DateTime.Now.ToString());
            swDAL.WriteLine(this.IdentLevels[1] + "/// ");
            swDAL.WriteLine(this.IdentLevels[1] + "/// http://layerbuilder.codeplex.com/");
            swDAL.WriteLine(this.IdentLevels[1] + "/// ");
            swDAL.WriteLine(this.IdentLevels[1] + "/// ");
            swDAL.WriteLine(this.IdentLevels[1] + "/// Remark: Use this class to write custom code.");
            swDAL.WriteLine(this.IdentLevels[1] + "/// </summary>");
            swDAL.WriteLine(this.IdentLevels[1] + "public class " +this.ClassName + "_DAL : " +this.ClassName + "_Base_DAL");
            swDAL.WriteLine(this.IdentLevels[1] + "{");

            swDAL.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swDAL.WriteLine(this.IdentLevels[2] + "/// Constructor");
            swDAL.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swDAL.WriteLine(this.IdentLevels[2] + "private " +this.ClassName + "_DAL() { }");
            swDAL.WriteLine(this.IdentLevels[0] + string.Empty);

            swDAL.WriteLine(this.IdentLevels[1] + "}");

            swDAL.WriteLine(this.IdentLevels[0] + "}");
            #endregion

            //
            swDALBase.Close();
            swDAL.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        private void GenerateBLL()
        {
            //
            StreamWriter swBLLBase;
            StreamWriter swBLL;
            string sBLLFolder = this.OutputFolder + this.DBLocalizedNamespacePath + "\\BLL";
            if (!Directory.Exists(sBLLFolder))
                Directory.CreateDirectory(sBLLFolder);
            swBLLBase = new StreamWriter(sBLLFolder + "\\" +this.ClassName + "_Base_SVC.cs", false);
            swBLL = new StreamWriter(sBLLFolder + "\\_temp_" +this.ClassName + "_SVC.cs", false);

            //
            #region BLLBase
            swBLLBase.WriteLine(this.IdentLevels[0] + "using System;");
            swBLLBase.WriteLine(this.IdentLevels[0] + "using System.Collections.Generic;");
            swBLLBase.WriteLine(this.IdentLevels[0] + "using System.Text;");
            swBLLBase.WriteLine(this.IdentLevels[0] + "using System.Reflection;");
            swBLLBase.WriteLine(this.IdentLevels[0] + "using System.ComponentModel;");
            swBLLBase.WriteLine(this.IdentLevels[0] + "using System.Data;");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);

            swBLLBase.WriteLine(this.IdentLevels[0] + "namespace " + this.Namespace + this.DBLocalizedNamespaceSuffix + ".BLL");
            swBLLBase.WriteLine(this.IdentLevels[0] + "{");

            swBLLBase.WriteLine(this.IdentLevels[1] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// Table " + this.Table);
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// ");
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// Generated by C# LayerBuilder");
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// " + DateTime.Now.ToString());
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// ");
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// http://layerbuilder.codeplex.com/");
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// ");
            swBLLBase.WriteLine(this.IdentLevels[1] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[1] + "public class " +this.ClassName + "_Base_SVC");
            swBLLBase.WriteLine(this.IdentLevels[1] + "{");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);

            #region Insert
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Insert entity");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Insert, true)]");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static int Insert(" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " o" + this.ClassName + ")");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Insert(o" + this.ClassName + ");");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Select
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Search entity");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Select, true)]");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static " + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " Select(" + sColumnsKeyParameter + ")");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Select(" + sColumnsKeyParameterPassthrow + ");");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Select_ Indexes
            foreach (string indexName in this.ColumnsIndexesParameterCollection.Keys)
            {
                swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
                swBLLBase.WriteLine(this.IdentLevels[2] + "/// Search entity by index " + indexName);
                swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
                swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Select, false)]");
                swBLLBase.WriteLine(this.IdentLevels[2] + "public static DataTable Select_" + indexName + "(" + (string)this.ColumnsIndexesParameterCollection[indexName] + ")");
                swBLLBase.WriteLine(this.IdentLevels[2] + "{");
                swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Select_" + indexName + "(" + (string)this.ColumnsIndexesParameterPassthrowCollection[indexName] + ");");
                swBLLBase.WriteLine(this.IdentLevels[2] + "}");
                swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            }
            #endregion

            #region Exists
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Check if entity exists");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static bool Exists(" + sColumnsKeyParameter + ")");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Exists(" + sColumnsKeyParameterPassthrow + ");");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Fill
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Fill entity list");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Fill, true)]");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static DataTable Fill()");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Fill();");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Fill_ Indexes
            foreach (string indexName in this.ColumnsIndexesParameterCollection.Keys)
            {
                swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
                swBLLBase.WriteLine(this.IdentLevels[2] + "/// Search entity by index " + indexName);
                swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
                swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Fill, false)]");
                swBLLBase.WriteLine(this.IdentLevels[2] + "public static DataTable Fill_" + indexName + "(" + (string)this.ColumnsIndexesParameterCollection[indexName] + ")");
                swBLLBase.WriteLine(this.IdentLevels[2] + "{");
                swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Fill_" + indexName + "(" + (string)this.ColumnsIndexesParameterPassthrowCollection[indexName] + ");");
                swBLLBase.WriteLine(this.IdentLevels[2] + "}");
                swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            }
            #endregion

            #region Update
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Update entity");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Update, true)]");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static int Update(" + this.DBLocalizedNamespacePrefix + "BE." + this.ClassName + " o" + this.ClassName + ")");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Update(o" + this.ClassName + ");");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region Delete
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Delete entity record");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Delete, true)]");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static int Delete(" + sColumnsKeyParameter + ")");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.Delete(" + sColumnsKeyParameterPassthrow + ");");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            #region DeleteAll
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// Delete entity ALL records");
            swBLLBase.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLLBase.WriteLine(this.IdentLevels[2] + "[DataObjectMethod(DataObjectMethodType.Delete, false)]");
            swBLLBase.WriteLine(this.IdentLevels[2] + "public static int DeleteAll()");
            swBLLBase.WriteLine(this.IdentLevels[2] + "{");
            swBLLBase.WriteLine(this.IdentLevels[3] + "return " + this.DBLocalizedNamespacePrefix + "DAL." + this.ClassName + "_DAL.DeleteAll();");
            swBLLBase.WriteLine(this.IdentLevels[2] + "}");
            swBLLBase.WriteLine(this.IdentLevels[0] + string.Empty);
            #endregion

            swBLLBase.WriteLine(this.IdentLevels[1] + "}");

            swBLLBase.WriteLine(this.IdentLevels[0] + "}");
            #endregion

            #region BLL
            swBLL.WriteLine(this.IdentLevels[0] + "using System;");
            swBLL.WriteLine(this.IdentLevels[0] + "using System.Collections.Generic;");
            swBLL.WriteLine(this.IdentLevels[0] + "using System.Text;");
            swBLL.WriteLine(this.IdentLevels[0] + "using System.Reflection;");
            swBLL.WriteLine(this.IdentLevels[0] + "using System.ComponentModel;");
            swBLL.WriteLine(this.IdentLevels[0] + "using System.Data;");
            swBLL.WriteLine(this.IdentLevels[0] + "using System.Linq;");
            swBLL.WriteLine(this.IdentLevels[0] + string.Empty);

            swBLL.WriteLine(this.IdentLevels[0] + "namespace " + this.Namespace + this.DBLocalizedNamespaceSuffix + ".BLL");
            swBLL.WriteLine(this.IdentLevels[0] + "{");

            swBLL.WriteLine(this.IdentLevels[1] + "/// <summary>");
            swBLL.WriteLine(this.IdentLevels[1] + "/// Table " + this.Table);
            swBLL.WriteLine(this.IdentLevels[1] + "/// ");
            swBLL.WriteLine(this.IdentLevels[1] + "/// Generated by C# LayerBuilder");
            swBLL.WriteLine(this.IdentLevels[1] + "/// " + DateTime.Now.ToString());
            swBLL.WriteLine(this.IdentLevels[1] + "/// ");
            swBLL.WriteLine(this.IdentLevels[1] + "/// http://layerbuilder.codeplex.com/");
            swBLL.WriteLine(this.IdentLevels[1] + "/// ");
            swBLL.WriteLine(this.IdentLevels[1] + "/// ");
            swBLL.WriteLine(this.IdentLevels[1] + "/// Remark: Use this class to write custom code.");
            swBLL.WriteLine(this.IdentLevels[1] + "/// </summary>");
            swBLL.WriteLine(this.IdentLevels[1] + "[DataObjectAttribute]");
            swBLL.WriteLine(this.IdentLevels[1] + "public class " +this.ClassName + "_SVC : " +this.ClassName + "_Base_SVC");
            swBLL.WriteLine(this.IdentLevels[1] + "{");

            swBLL.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swBLL.WriteLine(this.IdentLevels[2] + "/// Constructor");
            swBLL.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swBLL.WriteLine(this.IdentLevels[2] + "private " +this.ClassName + "_SVC() { }");
            swBLL.WriteLine(this.IdentLevels[0] + string.Empty);

            swBLL.WriteLine(this.IdentLevels[1] + "}");

            swBLL.WriteLine(this.IdentLevels[0] + "}");
            #endregion

            //
            swBLL.Close();
            swBLLBase.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        private void GenerateConnection()
        {
            //
            StreamWriter swConnection;
            string sConnectionFolder = this.OutputFolder + this.DBLocalizedNamespacePath;
            if (!Directory.Exists(sConnectionFolder))
                Directory.CreateDirectory(sConnectionFolder);
            swConnection = new StreamWriter(sConnectionFolder + "\\Connection.cs", false);

            //
            swConnection.WriteLine(this.IdentLevels[0] + "using System;");
            swConnection.WriteLine(this.IdentLevels[0] + "using System.Collections.Generic;");
            swConnection.WriteLine(this.IdentLevels[0] + "using System.Text;");
            swConnection.WriteLine(this.IdentLevels[0] + string.Empty);

            swConnection.WriteLine(this.IdentLevels[0] + "namespace " + this.Namespace + this.DBLocalizedNamespaceSuffix);
            swConnection.WriteLine(this.IdentLevels[0] + "{");

            swConnection.WriteLine(this.IdentLevels[1] + "/// <summary>");
            swConnection.WriteLine(this.IdentLevels[1] + "/// Generated by C# LayerBuilder");
            swConnection.WriteLine(this.IdentLevels[1] + "/// " + DateTime.Now.ToString());
            swConnection.WriteLine(this.IdentLevels[1] + "/// ");
            swConnection.WriteLine(this.IdentLevels[1] + "/// http://layerbuilder.codeplex.com/");
            swConnection.WriteLine(this.IdentLevels[1] + "/// ");
            swConnection.WriteLine(this.IdentLevels[1] + "/// </summary>");
            swConnection.WriteLine(this.IdentLevels[1] + "public static class Connection");
            swConnection.WriteLine(this.IdentLevels[1] + "{");

            swConnection.WriteLine(this.IdentLevels[2] + "private static string _connectionString = \"" + this.ConnectionString.Replace("\\", "\\\\") + "\";");
            swConnection.WriteLine(this.IdentLevels[0] + string.Empty);

            swConnection.WriteLine(this.IdentLevels[2] + "/// <summary>");
            swConnection.WriteLine(this.IdentLevels[2] + "/// Connection string");
            swConnection.WriteLine(this.IdentLevels[2] + "/// </summary>");
            swConnection.WriteLine(this.IdentLevels[2] + "public static string ConnectionString");
            swConnection.WriteLine(this.IdentLevels[2] + "{");
            swConnection.WriteLine(this.IdentLevels[3] + "get { return Connection._connectionString; }");
            swConnection.WriteLine(this.IdentLevels[2] + "}");

            swConnection.WriteLine(this.IdentLevels[1] + "}");

            swConnection.WriteLine(this.IdentLevels[0] + "}");

            //
            swConnection.Close();
        }

        /// <summary>
        /// 
        /// </summary>
        private void GenerateSQLScript()
        {
            //
            StreamWriter swSQLScript = null;

            string sSQLScriptFolder = this.OutputFolder + this.DBLocalizedNamespacePath + "\\SQL Script";
            if (!Directory.Exists(sSQLScriptFolder))
                Directory.CreateDirectory(sSQLScriptFolder);
            swSQLScript = new StreamWriter(sSQLScriptFolder + "\\SQLScript " + this.Namespace + " " + this.Table.ToUpper() + ".sql", false);

            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " INSERT");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_INSERT");
            swSQLScript.WriteLine(this.IdentLevels[1] + "(" + sColumnsKeySPSQLParameterVariables + ", " + sColumnsSPSQLInsertVariables + ")");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "INSERT INTO " + this.Table.ToUpper() + " (" + sColumnsSQLInsert + ")");
            swSQLScript.WriteLine(this.IdentLevels[2] + "VALUES (" + sColumnsSQLInsertVariables + ")");
            if (this.HasIdentityColumn)
            {
                swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);
                swSQLScript.WriteLine(this.IdentLevels[1] + "SELECT @@IDENTITY");
            }
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " SELECT");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_SELECT");
            swSQLScript.WriteLine(this.IdentLevels[1] + "(" + sColumnsKeySPSQLParameterVariables + ")");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper() + " WHERE " + sColumnsKeySQLParameterVariables);
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " EXISTS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_EXISTS");
            swSQLScript.WriteLine(this.IdentLevels[1] + "(" + sColumnsKeySPSQLParameterVariables + ")");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SELECT TOP 1 1 FROM " + this.Table.ToUpper() + " WHERE " + sColumnsKeySQLParameterVariables);
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " FILL");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_FILL");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper());
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            //
            foreach (string indexName in this.ColumnsIndexesParameterCollection.Keys)
            {
                swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " FILL " + indexName.ToUpper());
                swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_FILL_" + indexName.ToUpper());
                swSQLScript.WriteLine(this.IdentLevels[1] + "(" + (string)this.ColumnsIndexesSPSQLParameterVariablesCollection[indexName] + ")");
                swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
                swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
                swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
                swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

                swSQLScript.WriteLine(this.IdentLevels[1] + "SELECT " + sColumnsSQL + " FROM " + this.Table.ToUpper() + " WHERE " + this.ColumnsIndexesSQLParameterVariablesCollection[indexName]);
                swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

                swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
                swSQLScript.WriteLine(this.IdentLevels[0] + "END");
                swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
                swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);
            }

            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " UPDATE");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_UPDATE");
            swSQLScript.WriteLine(this.IdentLevels[1] + "(" + sColumnsKeySPSQLParameterVariables + ", " + sColumnsSPSQLSet + ")");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "UPDATE " + this.Table.ToUpper() + " SET " + sColumnsSQLSet + " WHERE " + sColumnsKeySQLParameterVariables);
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " DELETE");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_DELETE");
            swSQLScript.WriteLine(this.IdentLevels[1] + "(" + sColumnsKeySPSQLParameterVariables + ")");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "DELETE " + this.Table.ToUpper() + " WHERE " + sColumnsKeySQLParameterVariables);
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);


            //
            swSQLScript.WriteLine(this.IdentLevels[0] + "-- " + this.Table.ToUpper() + " DELETE ALL");
            swSQLScript.WriteLine(this.IdentLevels[0] + "CREATE PROCEDURE dbo.PRC_" + this.Table.ToUpper() + "_DELETEALL");
            swSQLScript.WriteLine(this.IdentLevels[0] + "AS");
            swSQLScript.WriteLine(this.IdentLevels[0] + "BEGIN");
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT ON");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            swSQLScript.WriteLine(this.IdentLevels[1] + "TRUNCATE TABLE " + this.Table.ToUpper());
            swSQLScript.WriteLine(this.IdentLevels[1] + string.Empty);
            
            swSQLScript.WriteLine(this.IdentLevels[1] + "SET NOCOUNT OFF");
            swSQLScript.WriteLine(this.IdentLevels[0] + "END");
            swSQLScript.WriteLine(this.IdentLevels[0] + "GO");
            swSQLScript.WriteLine(this.IdentLevels[0] + string.Empty);

            //
            swSQLScript.Close();
        }
    }
}

